package com.longway.core.ext.excel.util;

import com.longway.core.ext.excel.data.ColumnMeta;
import com.longway.core.ext.excel.data.TableDataset;
import com.longway.core.ext.excel.data.TableMeta;
import com.longway.core.ext.excel.error.ErrorException;

import java.sql.*;
import java.util.HashMap;
import java.util.List;
import java.util.Vector;

public class DbUtil
{
    private HashMap<String, String> tables = new HashMap<String, String>();
    private Connection dbConn = null;
    public static String tableName = "%";

    public DbUtil(Connection db)
    {
        dbConn = db;
        /*try
        {
        	if(!tableName.equals("virtrueTable")){
	            DatabaseMetaData dbma = db.getMetaData();
	            String[] types = new String[1];
	            types[0] = "TABLE";
	            ResultSet result = dbma.getTables(null, null, tableName, null);
	            while (result.next())
	            {
	                String tableName = result.getString(3);
	                String type = result.getString(4);
	                if ("table".equalsIgnoreCase(type))
	                    tables.put(tableName, tableName);
	            }
	            result.close();
        	}
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }*/
    }

    static public TableMeta getTableMeta(ResultSet rs)
    {

        TableKit table = new TableKit(rs);
        TableMeta meta = new TableMeta(tableName);
        for (int i = 0; i < table.size(); i++)
        {
            meta.addColumn(table.getColumn(i));
        }

        return meta;
    }

    private void refresh()
    {
        try
        {
            if (dbConn != null)
            {
                tables.clear();
                DatabaseMetaData dbma = dbConn.getMetaData();
                String[] types = new String[1];
                types[0] = "TABLE"; 
                ResultSet result = dbma.getTables(null, null, tableName, types);
                while (result.next())
                {
                    String tableName = result.getString(3);
                    String type = result.getString(4);
                    if ("table".equalsIgnoreCase(type)) {
                        tables.put(tableName, tableName);
                    }
                }
                result.close();
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }

    }

    public TableMeta getTableMeta(String table)
    {
        try
        {
            DatabaseMetaData dbma = dbConn.getMetaData();
            ResultSet result = dbma.getColumns(null, null, table, null);
            TableMeta meta = new TableMeta(table);
            while (result.next())
            {
               
                if (table.equalsIgnoreCase(result.getString(3)))
                {
                    String column = result.getString(4);
                    Integer type = result.getInt(5);
                    String typeName = result.getString(6);
                    Integer length = result.getInt(7);
                    Integer precision = result.getInt(10);
                    Integer nullable = result.getInt(11);
                    meta.addColumn(new ColumnMeta(column, typeName, type, length, precision, nullable));
                }
            }
            result.close();
            result = dbma.getPrimaryKeys(null, null, table);
            while (result.next())
            {
                String primaryColumn = result.getString(4);
                String primaryName = result.getString(6);
                meta.setPrimaryKey(primaryColumn);
            }
            return meta;
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        return null;
    }



    public boolean hasTable(String table)
    {
        refresh();
        if (tables.containsKey(table))
        {
            return true;
        }
        return false;
    }

    public void close()
    {
        tables.clear();
        if (dbConn != null) {
            try
            {
                dbConn.close();
            }
            catch (SQLException e)
            {
                e.printStackTrace();
            }
        }
         dbConn=null;
    }

    public Connection getConnection()
    {
        return dbConn;
    }



    public TableDataset executeQuery(String sql) throws ErrorException
    {
        Statement stat = null;
        ResultSet rs = null;
        try
        {
            stat = dbConn.createStatement();
            rs = stat.executeQuery(sql);
            TableDataset ds = new TableDataset();
            //TableMeta result = getTableMeta(rs);
            TableMeta result = getTableMeta(rs);
            ds.setTableMeta(result);
            
            while (rs.next())
            {
                List values = new Vector();
                for (int i = 1; i <= result.getColumns().size(); i++)
                {
                    values.add(rs.getObject(i));
                }
                ds.add(values);
            }

            rs.close();
            stat.close();
            return ds;
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            if (rs != null) {
                try
                {
                    rs.close();
                }
                catch (SQLException e1)
                {
                    e1.printStackTrace();
                }
            }
            if (stat != null) {
                try
                {
                    stat.close();
                }
                catch (SQLException e1)
                {
                    e1.printStackTrace();
                }
            }
            throw new ErrorException(210);
        }
    }
    
    public TableDataset executeQuery(String sql,Object ...params) throws ErrorException
    {
    	PreparedStatement stat = null;
        ResultSet rs = null;
        try
        {
            stat = dbConn.prepareStatement(sql);
            for (int i = 0; i < params.length; i++) {
            	stat.setObject(i+1, params[i]);
            }
            rs = stat.executeQuery();
            TableDataset ds = new TableDataset();
            //TableMeta result = getTableMeta(rs);
            TableMeta result = getTableMeta(rs);
            ds.setTableMeta(result);
            
            while (rs.next())
            {
                List values = new Vector();
                for (int i = 1; i <= result.getColumns().size(); i++)
                {
                    values.add(rs.getObject(i));
                }
                ds.add(values);
            }

            rs.close();
            stat.close();
            return ds;
        }
        catch (SQLException e)
        {
            e.printStackTrace();
            if (rs != null) {
                try
                {
                    rs.close();
                }
                catch (SQLException e1)
                {
                    e1.printStackTrace();
                }
            }
            if (stat != null) {
                try
                {
                    stat.close();
                }
                catch (SQLException e1)
                {
                    e1.printStackTrace();
                }
            }
            throw new ErrorException(210);
        }
    }
}

  
